﻿using System;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Net;
using System.Threading;
using Ionic.Zip;
using MySql.Data.MySqlClient;

namespace EmbeddedMySqlServer
{
    public class MySqlServer : IDisposable
    {
        private readonly string _serverRootPath;
        private const string MySqlMinuUrl = "http://garr.dl.sourceforge.net/project/miniserver/MiniServer/MiniServer_%20MySQL%205.0.67%20Portable/mini_server_11.zip";
        private readonly string _mySqlAdminPath;
        private readonly string _mySqldPath;
        private readonly string _serverinstallerFileName;
        private readonly string _myCnfPath;
        private readonly string _mySqlPath;
        private Process _serverprocess;
        private readonly string _mySqlExePath;
        private string _databaseName;
        private readonly string _mySqlPidPath;
        private int _port;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="serverRootPath">Absolute path to mysql files directory</param>
        public MySqlServer(string serverRootPath)
        {
            _serverRootPath = serverRootPath;

            _serverinstallerFileName = Path.Combine(_serverRootPath, "mini_server_11.zip");
            _mySqlPath = Path.Combine(_serverRootPath, @"mini_server_11\udrive");
            _mySqlPidPath = Path.Combine(_mySqlPath, "data", "mysql_mini.pid");
            _myCnfPath = Path.Combine(_mySqlPath, @"my.cnf");
            _mySqldPath = Path.Combine(_mySqlPath, @"bin\mysqld-opt.exe");
            _mySqlAdminPath = Path.Combine(_mySqlPath, @"bin\mysqladmin.exe");
            _mySqlExePath = Path.Combine(_serverRootPath, @"mini_server_11\udrive\bin\mysql.exe");
        }

        public void Start()
        {
            StopMySqlServer();
            InstallMySqlServer();
            StartMySqlServer();
        }

        public void Stop()
        {
            StopMySqlServer();
        }

        public int GetPort()
        {
            return _port;
        }

        private void InstallMySqlServer()
        {
            if (!Directory.Exists(_serverRootPath))
                Directory.CreateDirectory(_serverRootPath);

            if (!File.Exists(_serverinstallerFileName))
            {
                var webClient = new WebClient();
                webClient.DownloadFile(MySqlMinuUrl, _serverinstallerFileName);
            }

            ExtractZipArchive(_serverinstallerFileName, _serverRootPath);

            FindFreePort();

            PrepareMyCnf();
        }

        private void FindFreePort()
        {
            _port = 3311;
            while (!PortIsFree(_port))
            {
                _port++;
            }
        }

        private bool PortIsFree(int port)
        {
            var netstatResult = RunAndWait("cmd.exe", "/C netstat -anp tcp | FIND /I \"0.0.0.0:" + port + "\"");
            return string.IsNullOrWhiteSpace(netstatResult);
        }

        private void PrepareMyCnf()
        {
            var myCnfContent = File.ReadAllLines(_myCnfPath);
            myCnfContent[21] = "port=" + _port;
            myCnfContent[37] = "basedir = \"" + _mySqlPath + "\"";
            myCnfContent[37] = myCnfContent[37].Replace("\\", "/");
            myCnfContent[38] = "datadir = \"" + _mySqlPath + "\\data\\\"";
            myCnfContent[38] = myCnfContent[38].Replace("\\", "/");
            myCnfContent[39] = "tmpdir = \"" + _mySqlPath + "\\tmp\\\"";
            myCnfContent[39] = myCnfContent[39].Replace("\\", "/");
            File.WriteAllLines(_myCnfPath, myCnfContent);
        }

        private void StartMySqlServer()
        {
            _serverprocess = Run(_mySqldPath);
            File.WriteAllText(_mySqlPidPath, _serverprocess.Id.ToString(CultureInfo.InvariantCulture));
        }

        private void StopMySqlServer()
        {
            // close by PID in pidfile
            if (File.Exists(_mySqlPidPath))
            {
                RunAndWait("cmd.exe", "/C taskkill /F /PID " + File.ReadAllText(_mySqlPidPath));
                File.Delete(_mySqlPidPath);
            }

            // close by mysqladmin executable
            if (File.Exists(_mySqlAdminPath))
                RunAndWait(_mySqlAdminPath, "--port=" + _port + " --user=root --password=root shutdown");

            Thread.Sleep(500);
        }

        private void ExtractZipArchive(string serverinstallerFileName, string mySqlServerPath)
        {
            using (ZipFile serverInstallerArchive = ZipFile.Read(serverinstallerFileName))
                foreach (ZipEntry fileFromServerInstaller in serverInstallerArchive)
                    fileFromServerInstaller.Extract(mySqlServerPath, ExtractExistingFileAction.OverwriteSilently);
        }

        private Process Run(string mySqlStartScript)
        {
            var proc = new Process
            {
                StartInfo =
                {
                    FileName = mySqlStartScript,
                    RedirectStandardError = true,
                    RedirectStandardOutput = true,
                    UseShellExecute = false,
                    WorkingDirectory = _mySqlPath,
                }
            };
            proc.Start();
            return proc;
        }

        private string RunAndWait(string mySqlStartScript, string pa)
        {
            string output;
            using (var proc = new Process
            {
                StartInfo =
                {
                    Arguments = pa,
                    FileName = mySqlStartScript,
                    RedirectStandardError = true,
                    RedirectStandardOutput = true,
                    UseShellExecute = false,
                    CreateNoWindow = true,
                }
            })
            {
                proc.Start();
                output = proc.StandardOutput.ReadToEnd();
                proc.WaitForExit();
            }
            return output;
        }

        public void LoadDump(string databaseName, string extractedSqlFilePath)
        {
            _databaseName = databaseName;
            var d = RunAndWait("cmd.exe", "/C \"" + _mySqlExePath + " -uroot -proot " + databaseName + " < " + extractedSqlFilePath + "\"");
            if (!string.IsNullOrWhiteSpace(d))
            {
                Debug.WriteLine(d);
            }
        }

        public void DropDatabase(string databaseName)
        {
            var createDatabaseSql = string.Format("DROP DATABASE {0};", databaseName);
            var connection = new MySqlConnection("Data Source=localhost;Port=" + _port + ";UserId=root;PWD=root;");
            connection.Open();
            using (var command = new MySqlCommand(createDatabaseSql, connection))
                command.ExecuteNonQuery();
            connection.Close();
        }

        public void CreateDatabase(string databaseName)
        {
            var dropDatabaseSql = string.Format("DROP DATABASE IF EXISTS {0};", databaseName);
            var createDatabaseSql = string.Format("CREATE DATABASE {0};", databaseName);
            using (var connection = new MySqlConnection("Data Source=localhost;Port=" + _port + ";UserId=root;PWD=root;"))
            {
                connection.Open();
                try
                {
                    using (var command = new MySqlCommand(dropDatabaseSql, connection))
                        command.ExecuteNonQuery();
                    using (var command = new MySqlCommand(createDatabaseSql, connection))
                        command.ExecuteNonQuery();
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        public void Dispose()
        {
            Stop();
        }

        public void ExecuteNonQuery(string inputSql)
        {
            var useDatabaseSql = "USE " + _databaseName + ";";
            using (var connection = new MySqlConnection("Data Source=localhost;Port=" + _port + ";UserId=root;PWD=root;"))
            {
                try
                {
                    connection.Open();
                    try
                    {
                        using (var command = new MySqlCommand(useDatabaseSql, connection))
                            command.ExecuteNonQuery();
                    }
                    catch (Exception exception)
                    {
                        Debug.WriteLine(exception.Message);
                    }
                    try
                    {
                        using (var command = new MySqlCommand(inputSql, connection))
                            command.ExecuteNonQuery();
                    }
                    catch (Exception exception)
                    {
                        Debug.WriteLine(exception.Message);
                    }
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }
}
